﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Excel = Microsoft.Office.Interop.Excel; 

public partial class Retirement_Admin_applicationFormUpload : System.Web.UI.Page
{

 
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btn_submit_Click(object sender, ImageClickEventArgs e)
    {

        if (FileUploadControl.HasFile)
        {
                string filename = new AccountsManager().GenerateNo("FILE") + "_" + FileUploadControl.FileName;

                FileUploadControl.SaveAs(Server.MapPath("~/Retirement/Upload/") + filename);

                Session["R_applicationInfo"] = getExcelData(Server.MapPath("~/Retirement/Upload/") + filename);
            
        }

        
        this.Response.Redirect("./clientDetails.aspx?newClient=true");

    }

    private Dictionary<string, Object> getExcelData(string path)
    {
        /* 
        * Add reference > Browse > C: > Windows > assembly > GAC > Microsoft.Office.Interop.Excel > 12.0.0.0_etc > Microsoft.Office.Interop.Excel.dll
        * For School PC: 
        * Add reference > Browse > C: > Windows > assembly > GAC_MSIL > Microsoft.Office.Interop.Excel > 15.0.0.0__71e9bce111e9429c > Microsoft.Office.Interop.Excel.dll
        *
        * 
        */
        Dictionary<string, string> fundNameCodeMap = Fund.getFundNameCodeMap();
        Dictionary<string, Object> applicationInfo = new Dictionary<string, Object>();
        applicationInfo.Add("filePath", path);

        //Get fund allocation details
        List<Fund> choosenFundAllocation = new List<Fund>();

        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        Excel.Range range;


        int rCnt = 0;
        int cCnt = 0;

        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        range = xlWorkSheet.UsedRange;


        for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
        {
            for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
            {
                Object obj = (range.Cells[rCnt, cCnt] as Excel.Range).Value2;
                if (obj == null || !(obj is String)) continue;
                
                string lable = (string)obj;
                //Get personal details
                if (lable.Equals("First Name", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["firstName"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("Last Name", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["lastName"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("Nationality", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["nationality"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("NRIC/Passport", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["nric"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.StartsWith("DOB", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    //var dob = (range.Cells[rCnt, cCnt + 1] as Excel.Range).Text;
                    //applicationInfo["dob"] = Convert.ToDateTime(dob);
                    String dob = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                    applicationInfo["dob"] = DateTime.ParseExact(dob,"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture);
                }

                if (lable.Equals("Gender", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["gender"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("Marital Status", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["maritalStatus"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("Smoker Status", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["smokeStatus"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                //Get contact details
                if (lable.Equals("Mailing Address", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["mailingAddress"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("Postal Code", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["postalCode"] = Convert.ToString((range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2);
                }

                if (lable.Equals("Mobile No.", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["mobileNo"] = Convert.ToString((range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2);
                }

                if (lable.Equals("Home/Office No.", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["telNo"] = Convert.ToString((range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2);
                }

                if (lable.Equals("Email Address", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["email"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                //Get occupational details
                if (lable.Equals("Occupation", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["occupation"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("Specific Job Duties", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["jobDuties"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("Employer", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["employer"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("No. of Years", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["noOfYears"] = (Double)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("Industry", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["industry"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                //Get income details
                if (lable.StartsWith("Current Year", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["annualIncomeCurrent"] = (Double)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.StartsWith("1 Year Ago", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["annualIncomePrevious"] = (Double)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.StartsWith("Any income or remuneration", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["anyOtherIncomeSource"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("Source", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["otherIncomeSource"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.StartsWith("Annual earned income from source", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["otherSourceIncome"] = (Double)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.StartsWith("Please specify your source of wealth", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["sourceOfWealth"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.StartsWith("Are you an undischarged bankrupt?", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["bankruptcy"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }


                //Get plan details
                if (lable.Equals("Plan Name", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["planName"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("Basic Sum Insured", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["basicSumInsured"] = (Double)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("Critical Care Plus Rider", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["criticalCarePlusRider"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("Plus Rider Sum Insured", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["plusRiderSumInsured"] = (Double)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("Critical Care Waiver Rider", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["criticalCareWaiverRider"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }


                if (lable.StartsWith("Total Annual Premium", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["totalAnnualPremium"] = (Double)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }


                if (lable.StartsWith("Fund", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (!isCellNull(range, rCnt, cCnt + 1) && !isCellNull(range, rCnt, cCnt + 2))
                    {
                        Excel.Range fundNameCell = range.Cells[rCnt, cCnt + 1] as Excel.Range;

                        Excel.Range fundAllocationCell = range.Cells[rCnt, cCnt + 2] as Excel.Range;

                        if (fundAllocationCell.Value2 != null)
                        {
                            Decimal fundAllocation = Convert.ToDecimal((Double)fundAllocationCell.Value2);
                            String fundName = (String)fundNameCell.Value2;

                            if (fundAllocation > 0)
                            {
                                Fund fund = new Fund();
                                fund.code = fundNameCodeMap[fundName];
                                fund.name = fundName;
                                fund.allocation = fundAllocation/100;
                                
                                choosenFundAllocation.Add(fund);
                                
                            }

                        }
                    }
                 }

                //Get payment details
                if (lable.Equals("Payment Mode", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["paymentFrequency"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("Payment Method", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["paymentMethod"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

                if (lable.Equals("Payment Account", StringComparison.InvariantCultureIgnoreCase))
                {
                    if (isCellNull(range, rCnt, cCnt + 1)) continue;
                    applicationInfo["paymentAccount"] = (string)(range.Cells[rCnt, cCnt + 1] as Excel.Range).Value2;
                }

            }
        }

        applicationInfo["choosenFundAllocation"] = choosenFundAllocation;

        xlWorkBook.Close(true, null, null);
        xlApp.Quit();

        return applicationInfo;
    }

    private bool isCellNull(Excel.Range range, int row, int col)
    {
        Object obj = (range.Cells[row, col] as Excel.Range).Value2;
        if (obj == null)
        {
            return true;    
        }
        return false;
        
    }
 
}